package com.sq.partner.manager.controller;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.util.JsonUtils;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.excel.POIStyleWrapper;

/**
 * 
 * @author zzGuo@20160223 for Excel两个sheet页数据筛选
 */
@Controller
@RequestMapping(value = "/dataFilterManage")
public class DataFilterForExcelController {
	private static Logger logger = LogUtil.getLog(DataFilterForExcelController.class);
	
	@RequestMapping(value = "/dataFilteConfig")
	public String dataFilteConfig(ModelMap model) throws Exception {
		return "dataManage/dataFilterManage";
	}
	
	@RequestMapping(value = "/downloadDataFilterTemplet", method = RequestMethod.GET)
	public void downloadUserTemplet(HttpServletResponse response,HttpServletRequest request) throws IOException{
		String classPath=this.getClass().getResource("").getPath();
		String path="";
		path = (classPath.substring(0,classPath.indexOf("/WEB-INF/")+8)+ "/file/dataFilter_template.xls").replace("/WEB-INF", "");
		// 以流的形式下载文件
		InputStream inputStream=null;
		OutputStream outputStream =null;
		try {
			File file = new File(path);
			// 取得文件名。
			String filename = file.getName();
			inputStream = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[inputStream.available()];
			inputStream.read(buffer);
			//清空response
			response.reset();
			//设置response的Header
			response.addHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes()));
			response.addHeader("Content-Length", "" + file.length());
			outputStream = new BufferedOutputStream(response.getOutputStream());
			response.setContentType("application/octet-stream");
			outputStream.write(buffer);
			outputStream.flush();
		}catch (Exception e) {
			logger.error(e);
		}finally{
			try{
				if(null!=inputStream){
					inputStream.close();
				}
				if(null!=outputStream){
					outputStream.close();
				}
			}catch(Exception e1){
				logger.warn("关闭流失败！", e1);
			}
		}
	}
	
	@RequestMapping(value = "/dataFilteForExcelTwoSheetCompare")
	public void dataFilteForExcelTwoSheetCompare(@RequestParam MultipartFile[] myfiles,HttpServletResponse response) throws Exception {
		PrintWriter out = null;
		InputStream inputStream = null;
		OutputStream os = null;
		Page<String>  page = new Page<String>();
		List<HSSFRow> xlsLists = new ArrayList<HSSFRow>(10);//用于存储不一致的数据
		try {
			if (myfiles.length < 1) {
				page.setResult("error");
				page.setMessage("请添加上传文件!");
			}else{
				String fileNameStr = myfiles[0].getOriginalFilename();
				int lastDotIndex = fileNameStr.lastIndexOf(".");
				if(-1==lastDotIndex){
					throw new AppException("请选择扩展名为 .xls 的文件上传");
				}else{
					String extName = fileNameStr.substring(lastDotIndex+1,fileNameStr.length());
					if(!"xls".equals(extName.toLowerCase())){
						throw new AppException("请选择扩展名为 .xls 的文件上传");
					}
				}
				if (myfiles[0].isEmpty()) {
					throw new AppException("上传失败，上传文件不能为空！");
				} else {
					inputStream = myfiles[0].getInputStream();
				}
			}
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
			if (3 != hssfWorkbook.getNumberOfSheets()) {
				throw new AppException("上传Excel文件格式不正确，正确格式为有且仅有三个Sheet页");
			}
			HSSFSheet sourceSheet = hssfWorkbook.getSheetAt(0);
			HSSFSheet compareSheet = hssfWorkbook.getSheetAt(1);
			if (null == sourceSheet.getRow(0) || null == compareSheet.getRow(0)) {
				throw new AppException("两个Sheet页第一行不允许为空，请检查");
			} else if(sourceSheet.getLastRowNum()>2998 || compareSheet.getLastRowNum()>2998){
				throw new AppException("每个Sheet页只允许最大3000行数据比较，请检查两个Sheet页");
			} else if (sourceSheet.getRow(0).getLastCellNum() != compareSheet.getRow(0).getLastCellNum()) {
				throw new AppException("两个Sheet页第一行列格式不一致，请检查");
			}else if(sourceSheet.getRow(0).getLastCellNum()>5 || compareSheet.getRow(0).getLastCellNum()>5){
				throw new AppException("两个Sheet页第一行最多只允许5列，请检查");
			}
			HSSFRow head = sourceSheet.getRow(0);//表格表头信息
			xlsLists.add(head);//保存表头信息
			for (int rowNum = 1; rowNum <= sourceSheet.getLastRowNum(); rowNum++) {
				HSSFRow hssfRow = sourceSheet.getRow(rowNum);
				if (null == hssfRow) {
					continue;
				}
				boolean findFlag = false;
				for(int cRowNum = 1; cRowNum <= compareSheet.getLastRowNum(); cRowNum++){
					HSSFRow compareHssfRow = compareSheet.getRow(cRowNum);
					if (null == compareHssfRow) {
						continue;
					}
					if(hssfRow.getLastCellNum()!=compareHssfRow.getLastCellNum()){
						continue;
					}
					int eCount = 0;
					//比较两个sheet页两行数据是否完全相等
					for(int colNum = 0; colNum < compareHssfRow.getLastCellNum(); colNum++){
 						if(getCellText(hssfRow.getCell(colNum)).equals(getCellText(compareHssfRow.getCell(colNum)))){
 							eCount++;
 						}else{
 							break;
 						}
					}
					if(eCount == compareHssfRow.getLastCellNum()){
						findFlag = true;
						break;//限制只找一次
					}
				}
				int isNullorBlankCount = 0;
				if(!findFlag){//未找到且同时一行所有单元格都不为空则临时存储到容器
					for(int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++){
						if(null == hssfRow.getCell(colNum)){
							isNullorBlankCount++;
						}else if(Cell.CELL_TYPE_BLANK == hssfRow.getCell(colNum).getCellType()){
							isNullorBlankCount++;
						}
					}
					if(isNullorBlankCount!=hssfRow.getLastCellNum()){
						xlsLists.add(hssfRow);
					}
				}
			}
		//read end write
		if(xlsLists!=null && xlsLists.size()>1){
			HSSFWorkbook hwb = new HSSFWorkbook();
			// sheet 对应一个工作页
			HSSFSheet sheet = hwb.createSheet("比对结果表");
			//内容样式
			CellStyle contentStyle = new POIStyleWrapper(hwb).setNormalFont("宋体", 11).setSolidBorder().getCellStyle();
		    CellStyle contentNumFormatStyle = new POIStyleWrapper(hwb).setNormalFont("宋体", 11).setSolidBorder().setDataFormat("General").getCellStyle();

			for (int i = 0; i < xlsLists.size(); i++) {
				// 创建一行
				HSSFRow row = sheet.createRow(i);
				// 得到要插入的每一条记录
				HSSFRow  temRow = xlsLists.get(i);
				for (int colu = 0; colu <= temRow.getLastCellNum(); colu++) {
					if(null!=temRow.getCell(colu)){
						sheet.setColumnWidth(colu, 27*237);
						writeCellValue(temRow,row,colu,contentStyle,contentNumFormatStyle);
					}
				}
			}
			String fileName = java.net.URLEncoder.encode("CompareResults-"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date())+".xls","UTF-8");  
	        response.setHeader("Content-disposition","attachment; filename="+fileName);  
			response.setContentType("APPLICATION/OCTET-STREAM");
			os = response.getOutputStream();
	        hwb.write(os);
		}else{
			page.setResult("success");
			page.setMessage("[数据筛选表]中的所有数据都在[标准数据表]中存在");
		}
		} catch (Exception e) {
			logger.info(e);
			if(e instanceof AppException){
				page.setResult("error");
				page.setMessage(e.getMessage());
			}else if(e instanceof IllegalStateException){
				page.setResult("error");
				page.setMessage("格式转换失败，请尝试把所有的单元格转换为文本格式后重试");
			}else{
				page.setResult("error");
				page.setMessage("数据筛选失败，请稍后重试！");
			}
		} finally {
			try {
				if (null != inputStream) {
					inputStream.close();
				}
				if(null != os){
					os.flush();
			        os.close();
				}
				if(null == os){
					response.setContentType("text/html");
					response.setCharacterEncoding("UTF-8");
					out = response.getWriter();
					logger.info("++++++++++++++"+page.getMessage());
					out.write(JsonUtils.toJson(page));
					out.close();
				}
			} catch (Exception e) {
				logger.warn("关闭流失败！", e);
			}
		}
	}
	
	private String getCellText(Cell cell) throws Exception{
		 String val = "";
		 if(cell != null){
			 int cType = cell.getCellType();
			 if(Cell.CELL_TYPE_FORMULA == cType){
				 FormulaEvaluator evaluator = cell.getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
				 CellValue value = evaluator.evaluate(cell);
				 if(value == null){
					 return val;
				 }
				 cType = value.getCellType();
				 switch (cType) {
				    case Cell.CELL_TYPE_NUMERIC:
				    	val = String.valueOf(new BigDecimal(value.getNumberValue()));
				     break;
				    case Cell.CELL_TYPE_STRING://有时空白单元格会是string型
				    	 val = value.getStringValue();
				     break;
				     case Cell.CELL_TYPE_BOOLEAN:
				    	 val = String.valueOf(value.getBooleanValue());
				     break;
				     case Cell.CELL_TYPE_ERROR:
				    	 val = String.valueOf(value.getErrorValue());
				    break;
				 }
			 }else{
				 switch (cType) {
				    case Cell.CELL_TYPE_NUMERIC:
				    	val = String.valueOf(new BigDecimal(cell.getNumericCellValue()));
				     break;
				    case Cell.CELL_TYPE_STRING://有时空白单元格会是string型
				    	 val = cell.getStringCellValue();
				     break;
				     case Cell.CELL_TYPE_BOOLEAN:
				    	 val = String.valueOf(cell.getBooleanCellValue());
				     break;
				     case Cell.CELL_TYPE_ERROR:
				    	 val = String.valueOf(cell.getErrorCellValue());
				    break;
				 }
			 }
		 }
		 return val;
	 }
	private void writeCellValue(HSSFRow temRow, HSSFRow row,int colu,CellStyle contentStyle,CellStyle contentNumFormatStyle) throws Exception{
		if (Cell.CELL_TYPE_STRING == temRow.getCell(colu).getCellType()) {
			HSSFCell temCell = row.createCell(colu, Cell.CELL_TYPE_STRING);
			temCell.setCellStyle(contentStyle);
			temCell.setCellValue(temRow.getCell(colu).getStringCellValue());
		} else if (Cell.CELL_TYPE_NUMERIC == temRow.getCell(colu).getCellType()) {
			HSSFCell temCell = row.createCell(colu, Cell.CELL_TYPE_NUMERIC);
			if(HSSFDateUtil.isCellDateFormatted(temRow.getCell(colu))){
				temCell.setCellStyle(contentStyle);
				Date temD = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(temRow.getCell(colu).getNumericCellValue());  
				temCell.getCellStyle().setDataFormat(temRow.getCell(colu).getCellStyle().getDataFormat());
				logger.info("<<<>>>"+temRow.getCell(colu).getCellStyle().getDataFormat());
				temCell.setCellValue(temD);
			}else{
				temCell.setCellStyle(contentNumFormatStyle);
				temCell.setCellValue(new BigDecimal(temRow.getCell(colu).getNumericCellValue()).doubleValue());
			}
		} else if (Cell.CELL_TYPE_BOOLEAN == temRow.getCell(colu).getCellType()) {
			HSSFCell temCell = row.createCell(colu, Cell.CELL_TYPE_BOOLEAN);
			temCell.setCellStyle(contentStyle);
			temCell.setCellValue(temRow.getCell(colu).getBooleanCellValue());
		} else if (Cell.CELL_TYPE_ERROR == temRow.getCell(colu).getCellType()) {
			HSSFCell temCell = row.createCell(colu, Cell.CELL_TYPE_ERROR);
			temCell.setCellStyle(contentStyle);
			temCell.setCellValue(temRow.getCell(colu).getErrorCellValue());
		} else if(Cell.CELL_TYPE_FORMULA == temRow.getCell(colu).getCellType()){
			FormulaEvaluator evaluator = temRow.getCell(colu).getSheet().getWorkbook().getCreationHelper().createFormulaEvaluator();
			CellValue value = evaluator.evaluate(temRow.getCell(colu));
			if(null!=value){
				HSSFCell temCell = row.createCell(colu, value.getCellType());
				temCell.setCellStyle(contentStyle);
				switch (value.getCellType()){
			    case Cell.CELL_TYPE_NUMERIC:
			    	if(HSSFDateUtil.isCellDateFormatted(temRow.getCell(colu))){
						Date temD = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value.getNumberValue());  
						temCell.getCellStyle().setDataFormat(temRow.getCell(colu).getCellStyle().getDataFormat());
						logger.info("<<<>>>"+temRow.getCell(colu).getCellStyle().getDataFormat());
						temCell.setCellValue(temD);
					}else{
						temCell.setCellStyle(contentNumFormatStyle);
						temCell.setCellValue(value.getNumberValue());
					}
			    	break;
			    case Cell.CELL_TYPE_STRING://有时空白单元格会是string型
			    	temCell.setCellValue(value.getStringValue());
			    	break;
			    case Cell.CELL_TYPE_BOOLEAN:
			    	temCell.setCellValue(value.getBooleanValue());
			    	break;
			    case Cell.CELL_TYPE_ERROR:
			    	temCell.setCellValue(value.getErrorValue());
			    	break;
			 }
			}
		}
	}
}
